﻿using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Windows;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public struct vvVKPOS
    {
        public string pos;
        public double vkpqty;
        public double allocated;
        public int rolls;
        public double difference;
    }
    public class JLTVKWZReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public string ReportType = "ALLOCATED";
        List<vvVKPOS> listVKPOS = new List<vvVKPOS>();
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        public string lang = "CN";
        #endregion
        public JLTVKWZReport()
            : base()
        {
            Root = "C:\\IntexReport";
        }
      
        public void FillInExcelData(ArrayList columns, int xStart = 1, int yStart = 0)
        {
            exAgent.SetCurrentSheet("VVWZ");

            string msg = "";
            string sdkey = "";
            try
            {
                if (!dsSource.Tables.Contains("VAART"))
                {
                    return;
                }
                if (!dsSource.Tables.Contains("VAFARBEN"))
                {
                    return;
                }
                string src = "";
                string sgb = "";
                DataTable tbVKKOPF = dsSource.Tables["VKKOPF"];
                DataTable tbVKPOS = dsSource.Tables["VKPOS"];
                string KRS;
                string LFD;
                string POS;
                string TXTISIND;
                int position = 0;
                string currency = "RMB";
                DataRow dwVKKOPF = tbVKKOPF.Rows[0];
                DataTable tbVKPBEST = new DataTable(), tbVKK07 = new DataTable();
                DataTable TTGAAUF = new DataTable();
                string VKPMEEH = "";

                string printdate, orderdate = "";
                DataRow dwFinish = null, dwColor = null;
                DataRow dwWDECK = null, dwWASTAMM = null;
                string prdg = "";
                int positions = 0;
                DataTable tbVA07 = dsSource.Tables["VAART"];
                DataTable tbVA10 = dsSource.Tables["VAFARBEN"];
                DataRow dwVA07 = tbVA07.Rows[0];
                DataRow dwVA10 = null;
                double VKKQTY = 0;
                string meeh = "MTR";
                double vkpallocated = 0;
                DataTable tbVV = dsSource.Tables["VVLIEF"];
                string vvnumber = "";
                string str1 = "", str2 = "", str3 = "";
                if (tbVV != null)
                {
                    foreach (DataRow rowx in tbVV.Rows)
                    {
                        vvnumber = Helper.GetSubRollNum(rowx["VVLIEFSCH"].ToString());
                    }
                }
                exAgent.SetCellValue(39, 1, "留位清单");
                #region=============VKKOPF==================
                exAgent.RenameSheet(dwVKKOPF["VKKAUFKRS"].ToString() + "_" + System.Convert.ToInt64(dwVKKOPF["VKKAUFLFD"].ToString()));
                src = "确认号码/Proforma Invoice：" + dwVKKOPF["VKKAUFKRS"].ToString() + "/" + System.Convert.ToInt64(dwVKKOPF["VKKAUFLFD"].ToString());
                exAgent.SetCellValue(21, 1, src);
                //VKKBESTNR	VKKBESTDAT

                src = "贵司采购单/Your Order:";
                exAgent.SetCellValue(12, 30, src);
                src = dwVKKOPF["VKKBESTNR"].ToString();
                exAgent.SetCellValue(12, 31, src);
                src = "贵司落单/Order Date:";
                exAgent.SetCellValue(13, 30, src);
                src = dwVKKOPF["VKKBESTDAT"].ToString();
                exAgent.SetCellValue(13, 31, src);

                /*
                src = dwVKPOS1["VKPPOSTYP"].ToString();//"记号:" 
                exAgent.SetCellValue(3, 35, src);*/
                DataRow dwKD03 = null;
                var qkd = from dwTemp in dsSource.Tables["KDSTAMM"].AsEnumerable()
                          where dwTemp["KDKUNDNR"].ToString() == dwVKKOPF["VKKKUNDNR"].ToString()
                          select dwTemp;
                foreach (DataRow row in qkd)
                {
                    dwKD03 = row;
                }
                if (dwKD03 != null)
                {
                    src = "客户编号/Client No:";
                    exAgent.SetCellValue(11, 30, src);
                    exAgent.SetCellValue(11, 31, dwKD03["KDKUNDNR"].ToString());
                    src = dwKD03["KDKNAME"].ToString();
                    exAgent.SetCellValue(11, 1, Helper.GetGB2312Value(src).Trim());
                    src = dwKD03["KDNAME1"].ToString();
                    exAgent.SetCellValue(12, 1, Helper.GetGB2312Value(src).Trim());
                    src = dwKD03["KDSTRASSE1"].ToString();
                    exAgent.SetCellValue(13, 1, Helper.GetGB2312Value(src).Trim());
                    src = dwKD03["KDTEL"].ToString();
                    exAgent.SetCellValue(14, 1, Helper.GetGB2312Value(src).Trim());
                    src = dwKD03["KDFAX"].ToString();
                    exAgent.SetCellValue(15, 1, Helper.GetGB2312Value(src).Trim());
                    src = dwKD03["KDMAILADR"].ToString();
                    exAgent.SetCellValue(16, 1, Helper.GetGB2312Value(src).Trim());
                }
                currency = dwVKKOPF["VKKHWAEHRSL"].ToString();
                src = "联系人/Contact:";
                exAgent.SetCellValue(14, 30, src);
                exAgent.SetCellValue(14, 31, dwVKKOPF["VKKSACHBE"].ToString());

                exAgent.SetCellValue(3, 30, src);
                src = "付款条件/Terms of payment:";
                exAgent.SetCellValue(363, 15, src);

                src = dwVKKOPF["VKKZAHLBED"].ToString();
                var qz = from dwTemp in dsSource.Tables["TXTKON"].AsEnumerable()
                         where dwTemp["TXTIPIND"].ToString().Trim() == src.Trim() + "V"
                         select dwTemp;
                foreach (DataRow row in qz)
                {
                    exAgent.SetCellValue(363, 16, Helper.GetGB2312Value(row["TXT010"].ToString()));
                }

                src = "送货方式/Means of dispatch:";
                exAgent.SetCellValue(364, 15, src);

                src = dwVKKOPF["VKKVERSART"].ToString();
                sdkey = "00" + Left(src + "                                           ", 21) + "V";
                src = INTEXDBC.GetSDDAT("0208", sdkey);
                exAgent.SetCellValue(364, 16, Helper.GetGB2312Value(src.Substring(1, 10)));
                src = "发货条件/Delivery Terms:";
                exAgent.SetCellValue(365, 15, src);

                src = dwVKKOPF["VKKLIEFBED"].ToString();
                sdkey = "00" + Left(src + "                                           ", 21) + "V";
                src = INTEXDBC.GetSDDAT("0241", sdkey);
                exAgent.SetCellValue(365, 16, Helper.GetGB2312Value(src.Substring(1, 10)));
                #region============成件要求RECH===================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "RECH";
                var query1 = from dwTemp in dsSource.Tables["TXTKON"].AsEnumerable()
                             where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                             select dwTemp;
                if (query1.Count() > 0)
                {
                    tbVKK07 = query1.CopyToDataTable();
                }

                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {

                    //包装要求
                    src = "包装要求/Package:";
                    exAgent.SetCellValue(366, 15, src);
                    src = dwTXTKON["TXT010"].ToString();
                    sgb = GetGB2312Value(src);
                    SetTXTValue(sgb.TrimEnd(), 366, 16);

                    //匹长要求
                    src = "匹长要求/Roll length:";
                    exAgent.SetCellValue(367, 15, src);
                    src = dwTXTKON["TXT020"].ToString();
                    sgb = GetGB2312Value(src);
                    SetTXTValue(sgb.TrimEnd(), 367, 16);

                    //交货数
                    src = "溢短装/Over short allowance:";
                    exAgent.SetCellValue(368, 15, src);
                    src = dwTXTKON["TXT030"].ToString();
                    sgb = GetGB2312Value(src);
                    SetTXTValue(sgb.TrimEnd(), 368, 16);
                }
                #endregion
                string year = DateTime.Now.Year.ToString();
                string month = DateTime.Now.Month.ToString();
                string day = DateTime.Now.Day.ToString();

                printdate = "打印日期/Print Date:";
                exAgent.SetCellValue(21, 30, printdate);
                Helper.GetSplitDate(ref str1, ref str2, ref str3, date);
                exAgent.SetCellValue(21, 31, str3);

                #endregion
                #region ===================内在质量=====================
                /*
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "IVVT";
                var query = from dwTemp in dsSource.Tables["TXTKON"].AsEnumerable()
                            where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                            select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }

                string text = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + Helper.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd() + Environment.NewLine;
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }

                exAgent.SetCellValue(31, 5, text);
                */
                #endregion


                xStart = 41;
                int x = xStart;
                string finish = "";
                double TAmount = 0;
                string vkpmeeh = "";
                foreach (DataRow dwVKPOS in tbVKPOS.Rows)
                {
                    string color = "";
                    positions++;
                    msg = "开始写入：" + position.ToString() + "行";
                    EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.RunningProgress);
                    KRS = dwVKPOS["VKPAUFKRS"].ToString();
                    LFD = dwVKPOS["VKPAUFLFD"].ToString();
                    POS = dwVKPOS["VKPAUFPOS"].ToString();
                    string vkps = KRS + "-" + Helper.GetSubRollNum(LFD) + "-" + Helper.GetSubRollNum(POS);
                    var qv = from vvVKPOS vkp in listVKPOS.AsQueryable()
                             where vkp.pos.StartsWith(vkps)
                             select vkp;

                    vvVKPOS vvkp = new vvVKPOS();
                    foreach (vvVKPOS vkp in qv)
                    {
                        vvkp = vkp;
                    }
                    if (vvkp.allocated == 0)
                        continue;
                    exAgent.SetCellValue(x, 1, System.Convert.ToInt64(POS));

                    #region====================VKPOS=======================

                    src = dwVKPOS["VKPART"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(x, 3, sgb);
                    src = dwVKPOS["VKPFARBE"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    color += sgb.Trim();
                    #endregion
                    #region================组织===================

                    dwVA07 = null;
                    var q07 = from DataRow row in tbVA07.AsEnumerable()
                              where row["VAAART"].ToString() == dwVKPOS["VKPART"].ToString()
                              select row;
                    foreach (DataRow row in q07)
                    {
                        dwVA07 = row;
                    }
                    dwVA10 = null;
                    var q10 = from DataRow row in tbVA10.AsEnumerable()
                              where row["VAFART"].ToString() == dwVKPOS["VKPART"].ToString()
                              && row["VAFFARBE"].ToString() == dwVKPOS["VKPFARBE"].ToString()
                              select row;
                    foreach (DataRow row in q10)
                    {
                        dwVA10 = row;
                    }
                    src = dwVA10["VAFBEZ"].ToString().Trim();
                    color += "/" + Helper.GetGB2312Value(src).Trim();
                    src = dwVA10["VAFBESCHICH"].ToString().Trim();
                    sdkey = "00" + Left(src + "                                           ", 21);
                    src = INTEXDBC.GetSDDAT("0275", sdkey);
                    src = Helper.GetGB2312Value(src.Substring(1, 10)).Trim();
                    if (!finish.Contains(src + ";"))
                        finish += src + ";";
                    color += src.Trim();
                    exAgent.SetCellValue(x, 8, color.Trim());
                    #endregion
                    #region================客人花号/品名======================
                    src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDBEZ"].ToString().TrimEnd();
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(x + 1, 3, sgb);
                    #endregion
                    #region================数量===================
                    src = dwVKPOS["VKPMENGE1"].ToString();
                    string VKPMENGE1 = Divide(src, 100);
                    VKKQTY += System.Convert.ToDouble(VKPMENGE1);
                    vkpmeeh = dwVKPOS["VKPMEEH"].ToString();
                    exAgent.SetCellValue(x, 17, VKPMENGE1 + vkpmeeh);
                    if (VKPMEEH.Trim() == "YRD")
                    {
                        //VKPMENGE1 = (System.Math.Round(System.Convert.ToDouble(VKPMENGE1) * 0.9144, 0)).ToString();
                    }
                    meeh = dwVKPOS["VKPPREH"].ToString();
                    /*foreach (vvVKPOS vkp in qv)
                    {
                        exAgent.SetCellValue(x, 23, vkp.allocated);
                        vkpallocated += vkp.allocated;
                        exAgent.SetCellValue(x, 28, System.Convert.ToDouble(VKPMENGE1) - vkp.allocated);
                    }*/
                    decimal shipped = System.Math.Round(System.Convert.ToDecimal(dwVKPOS["VKPZUGET"].ToString()) / 100, 1);//留位数量
                    decimal tshipped = System.Math.Round(System.Convert.ToDecimal(dwVKPOS["VKPFREIGE"].ToString()) / 100, 1);
                    if (VKPMEEH.Trim() == "YRD")
                    {
                        //allocated = allocated/ System.Convert.ToDecimal(0.9144);
                    }
                    exAgent.SetCellValue(40, 17, "Quantity" + Environment.NewLine + "客户数量");
                    //exAgent.SetCellValue(40, 23, "Shipped" + Environment.NewLine + "生产数量");
                    exAgent.SetCellValue(40, 28, "Picking" + Environment.NewLine + "发货数量");
                    //exAgent.SetCellValue(x, 23, tshipped);
                    exAgent.SetCellValue(x, 28, shipped);
                    #endregion
                    #region =================交期======================
                    exAgent.SetCellValue(x, 36, dwVKPOS["VKPLIEFTERM"].ToString());
                    //船样 
                    #endregion
                    x = x + 2;
                }
                if (meeh.Trim() == "YRD")
                {
                    VKKQTY = System.Math.Round(System.Convert.ToDouble(VKKQTY) * 0.9144, 0);
                }
                exAgent.SetCellValue(361, 22, "总数量/Total quantity:" + VKKQTY + vkpmeeh);
                exAgent.SetCellValue(361, 23, vkpallocated);
                //exAgent.SetCellValue(940, 46, prdg);
            }
            catch (Exception ex)
            {
                msg = "有错误产生，请注意:";
                EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
                Tracer.Error(ex.StackTrace, ex);
                MessageBox.Show(msg + ex.Message);
            }
            finally
            {
                msg = "指令表虽然已经生产，但请注意核对。";
                EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.RunningProgress);
            }
        }
        private void FillInEXCEL(int colums)
        {
            try
            {
                DataTable tbWB = dsSource.Tables["WBBST"];
                DataTable tbVKP = dsSource.Tables["VKPOS"];
                DataTable tbVVT = dsSource.Tables["VVTLIEF"];
                Dictionary<string, string> makeup = new Dictionary<string, string>();
                Dictionary<string, string> packup = new Dictionary<string, string>();
                int rls = tbWB.Rows.Count;
                string str1 = "", str2 = "", str3 = "";
                //exAgent.SetCellValue(39, 1, "留位清单");
                var qvv = from DataRow RX in tbVVT.AsEnumerable()
                          select RX; 
                string KRS, LFD, POS, LOT;
                foreach (DataRow dwVVT in qvv)
                {
                    DataRow dwVKPOS = null;
                    KRS = dwVVT["VVTAUFKRS"].ToString();
                    LFD = dwVVT["VVTAUFLFD"].ToString();
                    POS = dwVVT["VVTAUFPOS"].ToString();
                    LOT = dwVVT["VVTTEILNR"].ToString();
                    var query = from DataRow row in tbVKP.AsEnumerable()
                                where row["VKPAUFKRS"].ToString() == KRS
                                && row["VKPAUFLFD"].ToString() == LFD
                                && row["VKPAUFPOS"].ToString() == POS
                                select row;
                    foreach (DataRow row in query)
                    {
                        dwVKPOS = row;
                    }
                    if (dwVKPOS == null)
                        continue;
                    string src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDBEZ"].ToString().TrimEnd();
                    string cart = GetGB2312Value(src);
                    string vkpart = dwVKPOS["VKPART"].ToString().TrimEnd() + "-" + dwVKPOS["VKPFARBE"].ToString();
                    string sheet = KRS + "-" + Helper.GetSubRollNum(LFD) + "-" + Helper.GetSubRollNum(POS) + "-" + Helper.GetSubRollNum(LOT);
                    string UM = "Y";
                    if (dwVKPOS["VKPMEEH"].ToString().Contains("M"))
                        UM = "M";
                    string fileters = "WBBAUFKRS='" + KRS + "' and WBBAUFLFD='" + LFD + "' and WBBAUFPOS='" + POS + "' AND WBBTP='" + LOT + "'";
                    DataRow[] frolls = tbWB.Select(fileters);
                    if (frolls.Length > 0)
                    {
                        exAgent.SetCurrentSheet("VKWZ");
                        exAgent.CopySheet(sheet);
                    }
                    else
                    {
                        continue;
                    }
                    int maxlines = 39;
                    int x = 1;
                    int y = 1;
                    int pages = 0;
                    int turns = 0;
                    int rolls = 0;
                    int reserved = 12;
                    int TRolls = 0;
                    double vkptotal = 0;
                    Helper.GetSplitDate(ref str1, ref str2, ref str3, date);
                    exAgent.SetCurrentSheet(sheet);
                    #region=====VAART===========
                    DataTable tbVA = dsSource.Tables["VAART"];
                    DataRow dwVA07 = null;
                    string koll = dwVKPOS["VKPKOLL"].ToString();
                    string art = dwVKPOS["VKPART"].ToString();
                    DataRow[] rows = tbVA.Select("VAAKOLL='" + koll + "' AND VAAART='" + art + "'");
                    foreach (DataRow row in rows)
                    {
                        dwVA07 = row;
                    }
                    string articleinfor = "";
                    string sdkey = "";
                    DataTable tbVKK07 = new DataTable();
                    if (dwVA07 != null)
                    {
                        src = dwVA07["VAAARTGRP"].ToString();
                        sdkey = "00" + Left(src + "                                           ", 21) + "V";
                        src = INTEXDBC.GetSDDAT("0178", sdkey);
                        articleinfor += GetGB2312Value(src.Substring(1, 20)).Trim() + " ";

                        string text = "";
                        string TXTISIND = koll + art.Trim();
                        var qvad = from dwTemp in dsSource.Tables["TXTKON"].AsEnumerable()
                                   where dwTemp["TXTIPIND"].ToString().Trim() == TXTISIND
                                   select dwTemp;
                        if (qvad.Count() > 0)
                        {
                            tbVKK07 = qvad.CopyToDataTable();
                        }
                        foreach (DataRow dwTXTKON in tbVKK07.Rows)
                        {
                            for (int ti = 1; ti < 40; ti++)
                            {
                                string ci = "TXT" + Right("00" + ti, 2) + "0";
                                src = dwTXTKON[ci].ToString(); src = GetGB2312Value(src).Trim();
                                if (src == "")
                                    break;
                                text += src;
                            }
                        }
                        articleinfor += text.Trim() + " ";

                        //exAgent.SetCellValue(6, 16, src);
                        string vbrt = dwVA07["VAAMEBRT"].ToString().Trim();
                        src = dwVA07["VAABRTBIS"].ToString();
                        double v2 = System.Math.Round(System.Convert.ToDouble(src) * 0.01, 0);
                        articleinfor += (v2 - 1) + "-" + v2 + vbrt.Trim();
                    }
                    exAgent.SetCellValue(5, 1, articleinfor);
                    #endregion
                    vvVKPOS vvvkp = FillInWBBST(exAgent, frolls.CopyToDataTable(), sheet, vkpart, cart, UM, lang);
                    listVKPOS.Add(vvvkp);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("完成", StatusEventType.StartProgress);
            }

        }
        private void FillInEXCELFREE(int colums)
        {
            try
            {
                DataTable tbWB = dsSource.Tables["WBBST"];
                DataTable tbVKP = dsSource.Tables["VKPOS"];
                DataTable tbVVT = dsSource.Tables["VVTLIEF"];
                Dictionary<string, string> makeup = new Dictionary<string, string>();
                Dictionary<string, string> packup = new Dictionary<string, string>();
                int rls = tbWB.Rows.Count;
                string str1 = "", str2 = "", str3 = "";
                //exAgent.SetCellValue(39, 1, "留位清单");
                var qxd = from XD in tbWB.AsEnumerable()
                          where  
                         XD["WBBAUFLFD"].ToString().Trim() =="000000"
                          group XD by new
                          {
                              ART = XD["WBBART"].ToString(),
                              FARBE = XD["WBBFARBE"].ToString(),
                          }
                              into g
                              select new
                              {
                                  KEY = g.Key,
                              }; 
                string KRS, LFD, POS, LOT;
                foreach (var dwVVT in qxd)
                {
                    DataRow dwVKPOS = null;
                    string WBART = dwVVT.KEY.ART;
                    string FARBE = dwVVT.KEY.FARBE;
                 
                    var query = from DataRow row in tbVKP.AsEnumerable()
                                where row["VKPART"].ToString().Trim() == WBART.Trim()
                                && row["VKPFARBE"].ToString().Trim() ==FARBE.Trim()
                                select row;
                    foreach (DataRow row in query)
                    {
                        dwVKPOS = row;
                    }
                    if (dwVKPOS == null)
                        continue;
                    KRS = dwVKPOS["VKPAUFKRS"].ToString();
                    LFD = dwVKPOS["VKPAUFLFD"].ToString();
                    POS = dwVKPOS["VKPAUFPOS"].ToString();
                    LOT = "00";
                    string src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd() + "/" + dwVKPOS["VKPKDBEZ"].ToString().TrimEnd();
                    string cart = GetGB2312Value(src);
                    string vkpart = dwVKPOS["VKPART"].ToString().TrimEnd() + "-" + dwVKPOS["VKPFARBE"].ToString();
                    string sheet = KRS + "-" + Helper.GetSubRollNum(LFD) + "-" + Helper.GetSubRollNum(POS) + "-" + Helper.GetSubRollNum(LOT);
                    string UM = "Y";
                    if (dwVKPOS["VKPMEEH"].ToString().Contains("M"))
                        UM = "M";
                    string fileters = "WBBART='" + WBART + "' and WBBFARBE='" + FARBE + "' and WBBAUFLFD='000000'";
                    DataRow[] frolls = tbWB.Select(fileters);
                    if (frolls.Length > 0)
                    {
                        exAgent.SetCurrentSheet("VKWZ");
                        exAgent.CopySheet(sheet);
                    }
                    else
                    {
                        continue;
                    }
                    int maxlines = 39;
                    int x = 1;
                    int y = 1;
                    int pages = 0;
                    int turns = 0;
                    int rolls = 0;
                    int reserved = 12;
                    int TRolls = 0;
                    double vkptotal = 0;
                    Helper.GetSplitDate(ref str1, ref str2, ref str3, date);
                    exAgent.SetCurrentSheet(sheet);
                    #region=====VAART===========
                    DataTable tbVA = dsSource.Tables["VAART"];
                    DataRow dwVA07 = null;
                    string koll = dwVKPOS["VKPKOLL"].ToString();
                    string art = dwVKPOS["VKPART"].ToString();
                    DataRow[] rows = tbVA.Select("VAAKOLL='" + koll + "' AND VAAART='" + art + "'");
                    foreach (DataRow row in rows)
                    {
                        dwVA07 = row;
                    }
                    string articleinfor = "";
                    string sdkey = "";
                    DataTable tbVKK07 = new DataTable();
                    if (dwVA07 != null)
                    {
                        src = dwVA07["VAAARTGRP"].ToString();
                        sdkey = "00" + Left(src + "                                           ", 21) + "V";
                        src = INTEXDBC.GetSDDAT("0178", sdkey);
                        articleinfor += GetGB2312Value(src.Substring(1, 20)).Trim() + " ";

                        string text = "";
                        string TXTISIND = koll + art.Trim();
                        var qvad = from dwTemp in dsSource.Tables["TXTKON"].AsEnumerable()
                                   where dwTemp["TXTIPIND"].ToString().Trim() == TXTISIND
                                   select dwTemp;
                        if (qvad.Count() > 0)
                        {
                            tbVKK07 = qvad.CopyToDataTable();
                        }
                        foreach (DataRow dwTXTKON in tbVKK07.Rows)
                        {
                            for (int ti = 1; ti < 40; ti++)
                            {
                                string ci = "TXT" + Right("00" + ti, 2) + "0";
                                src = dwTXTKON[ci].ToString(); src = GetGB2312Value(src).Trim();
                                if (src == "")
                                    break;
                                text += src;
                            }
                        }
                        articleinfor += text.Trim() + " ";

                        exAgent.SetCellValue(6, 16, src);
                        string vbrt = dwVA07["VAAMEBRT"].ToString().Trim();
                        src = dwVA07["VAABRTBIS"].ToString();
                        double v2 = System.Math.Round(System.Convert.ToDouble(src) * 0.01, 0);
                        articleinfor += (v2 - 1) + "-" + v2 + vbrt.Trim();
                    }
                    exAgent.SetCellValue(5, 1, articleinfor);
                    #endregion
                    vvVKPOS vvvkp = FillInWBBST(exAgent, frolls.CopyToDataTable(), sheet, vkpart, cart, UM, lang);
                    listVKPOS.Add(vvvkp);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("完成", StatusEventType.StartProgress);
            }

        }
        public vvVKPOS FillInWBBST(ExcelX.ExcelLib.ExcelAgent m_exAgent, DataTable tbWB, string sheet, string vkpart, string cart, string UM, string m_Lang)
        {
            double vkptotal = 0;
            string processed = "";
            vvVKPOS vvvkp = new vvVKPOS();
            try
            {
                int maxlines = 39;
                int x = 1;
                int y = 1;
                int pages = 0;
                int turns = 0;
                int rolls = 0;
                int reserved = 12;
                int TRolls = 0;
                if (m_Lang == "EN")
                {
                    m_exAgent.SetCellValue(3, 8, "Allocation");
                    m_exAgent.SetCellValue(1, 23, "Order:" + sheet);
                    m_exAgent.SetCellValue(2, 23, "Article:" + vkpart);
                    m_exAgent.SetCellValue(1, 30, "Print:" + date);
                    m_exAgent.SetCellValue(3, 20, "Customer Art:" + cart);
                    m_exAgent.SetCellValue(pages * 61 + 11, 1, "C/NO.");
                    m_exAgent.SetCellValue(pages * 61 + 11, 3, "LENGTH");
                    m_exAgent.SetCellValue(pages * 61 + 11, 6, "LOT");
                    m_exAgent.SetCellValue(pages * 61 + 11, 8, "PATTERN");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 1, "C/NO.");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 3, "LENGTH");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 6, "LOT");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 8, "PATTERN");

                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 1, "C/NO.");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 3, "LENGTH");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 6, "LOT");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 8, "PATTERN");

                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 1, "C/NO.");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 3, "LENGTH");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 6, "LOT");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 8, "PATTERN");
                }
                else
                {
                    m_exAgent.SetCellValue(3, 8, "留位清单");
                    m_exAgent.SetCellValue(1, 23, "销售单号:" + sheet);
                    m_exAgent.SetCellValue(2, 23, "品       种:" + vkpart);
                    m_exAgent.SetCellValue(1, 30, "打印:" + date);
                    m_exAgent.SetCellValue(3, 20, "客人品种:" + cart);
                    m_exAgent.SetCellValue(pages * 61 + 11, 1, "卷号");
                    m_exAgent.SetCellValue(pages * 61 + 11, 3, "匹长");
                    m_exAgent.SetCellValue(pages * 61 + 11, 6, "分色");
                    m_exAgent.SetCellValue(pages * 61 + 11, 8, "格型");

                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 1, "卷号");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 3, "匹长");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 6, "分色");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 8, "格型");

                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 1, "卷号");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 3, "匹长");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 6, "分色");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 8, "格型");

                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 1, "卷号");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 3, "匹长");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 6, "分色");
                    m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 8, "格型");
                }

                double lineqty = 0;
                int lineroll = 0;
              
                foreach (DataRow rwb in tbWB.Rows)
                {
                    //pages =0,rolls=0,turns=0
                    //pages=0,rolls=37
                    if (rolls > maxlines)
                    {
                        rolls = 0;
                        lineqty = 0;
                        lineroll = 0;
                        turns++;
                        if (turns > 3)
                        {
                            turns = 0;
                            pages++;
                            #region=====Header===

                            m_exAgent.RangeCopy(11, 61, pages * 61 + 11, (pages + 1) * 61, true);
                            if (m_Lang == "EN")
                            {
                                m_exAgent.SetCellValue(pages * 61 + 11, 1, "C/NO.");
                                m_exAgent.SetCellValue(pages * 61 + 11, 3, "LENGTH");
                                m_exAgent.SetCellValue(pages * 61 + 11, 6, "LOT");
                                m_exAgent.SetCellValue(pages * 61 + 11, 8, "PATTERN");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 1, "C/NO.");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 3, "LENGTH");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 6, "LOT");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 8, "PATTERN");

                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 1, "C/NO.");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 3, "LENGTH");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 6, "LOT");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 8, "PATTERN");

                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 1, "C/NO.");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 3, "LENGTH");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 6, "LOT");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 8, "PATTERN");
                            }
                            else
                            {
                                m_exAgent.SetCellValue(pages * 61 + 11, 1, "卷号");
                                m_exAgent.SetCellValue(pages * 61 + 11, 3, "匹长");
                                m_exAgent.SetCellValue(pages * 61 + 11, 6, "分色");
                                m_exAgent.SetCellValue(pages * 61 + 11, 8, "格型");

                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 1, "卷号");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 3, "匹长");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 6, "分色");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 1 + 8, "格型");

                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 1, "卷号");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 3, "匹长");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 6, "分色");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 2 + 8, "格型");

                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 1, "卷号");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 3, "匹长");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 6, "分色");
                                m_exAgent.SetCellValue(pages * 61 + 11, 9 * 3 + 8, "格型");
                            }
                            #endregion
                        }
                    }
                    if (pages > 0)
                    {
                        reserved = 12;
                        maxlines = 39;
                    }
                    x = pages * 61 + reserved + rolls;
                    y = turns * 9 + 1;

                    string stk = rwb["WBBSTKNR"].ToString();
                    double stkweight = 0;
                    double stkgrossw = 0;
                    //INTEXDBC.GetStkWeight(rwb, ref  stkgrossw, ref  stkweight, ref makeup, ref  packup);

                    double RAWLG = System.Convert.ToDouble(rwb["WBBISTKLG"].ToString()) * 0.001;
                    double STKLG = System.Math.Round(RAWLG, 1);
                    double YLength = System.Math.Round(RAWLG / 0.9144, 1);
                    string cardno = rwb["WBBRWLFD"].ToString().Trim();
                    string packnr = rwb["WBBPACKNR"].ToString().Trim();
                    
                    if (packnr != "0000")
                    {
                        if (processed.Contains(packnr+";")) continue;
                        processed += packnr + ";";
                        stk = Helper.GetSubRollNum(packnr);
                        var qxd = from XD in tbWB.AsEnumerable()
                                  where XD["WBBPACKNR"].ToString() == packnr
                                  group XD by new
                                  {
                                      WBBPACKNR = XD["WBBPACKNR"].ToString(),
                                  }
                                      into g
                                      select new
                                      {
                                          KEY = g.Key,
                                          RAWLG = g.Sum(XD => XD.Field<decimal>("WBBISTKLG")),
                                      };
                        if (qxd.Count() > 0)
                        {
                            foreach (var xd in qxd)
                            {
                                RAWLG =0.001*(double) xd.RAWLG;
                                STKLG = System.Math.Round(RAWLG, 1);
                            }
                        }
                    }
                    else
                    {
                        stk = Helper.GetAbsRollNum(stk, cardno);
                    }
                    
                    if (UM == "Y")
                        STKLG = YLength;
                    m_exAgent.SetCellValue(x, y, stk);
                    m_exAgent.SetCellValue(x, y + 2, STKLG);
                    m_exAgent.SetCellValue(x, y + 5, rwb["WBBTAX"].ToString().Trim());
                    m_exAgent.SetCellValue(x, y + 7, rwb["WBBDPART"].ToString().Trim());
                    //m_exAgent.SetCellValue(x, y + 8, stkgrossw);
                    rolls++;
                    TRolls++;
                    lineqty += STKLG;
                    lineroll++;
                    x = pages * 61 + 52;
                    y = turns * 9 + 1;
                    //m_exAgent.SetCellValue(x, y + 2, lineqty + UM);
                    m_exAgent.SetCellValue(x, y + 5, lineroll + "PCS");
                    vkptotal += STKLG;
                }
                if (m_Lang == "EN")
                {
                    m_exAgent.SetCellValue(5, 21, "Total:" + System.Math.Round(vkptotal, 1) + UM + ";" + TRolls + "PCS");
                }
                else
                {
                    m_exAgent.SetCellValue(5, 21, "总长度:" + System.Math.Round(vkptotal, 1) + UM + ";总卷数" + TRolls + "个");
                }

                vvvkp.pos = sheet;
                vvvkp.allocated = System.Math.Round(vkptotal, 1);
                vvvkp.rolls = TRolls;
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("完成", StatusEventType.StopProgress);
            }
            return vvvkp;
        }
        private void FillinFooter(int rows)
        {
            try
            {
                //string footer = "           审单:	          发货:           核对:	          司机:           收货:             ";
                /*
                exAgent.SetCellValue(rows + 4, 3, "制单：");
                exAgent.SetCellValue(rows + 4, 7, "审单：");
                exAgent.SetCellValue(rows + 4, 11, "发货：");
                exAgent.SetCellValue(rows + 4, 15, "核对：");
                exAgent.SetCellValue(rows + 4, 19, "司机：");
                exAgent.SetCellValue(rows + 4, 23, "收货：");*/
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

        }
        private void VV06Header()
        {
            try
            {
                #region=========Variable==============================
                int row = 7;
                string kdname = "";
                //int packrow = 1999;
                DataTable tbVVLIEF = dsSource.Tables["VVLIEF"];
                DataTable tbKUNDADR = dsSource.Tables["KUNDADR"];
                DataTable tbKDSTAMM = dsSource.Tables["KDSTAMM"];
                DataTable tbVKKOPF = dsSource.Tables["VKKOPF"];
                DataTable tbVKPOS = dsSource.Tables["VKPOS"];
                DataTable tbKDANSP = dsSource.Tables["KDANSP"];
                DataRow dwVVLIEF = tbVVLIEF.Rows[0];
                #endregion

                #region=========收件人地址==============================
                string receiver = "";
                string KUNDNR = dwVVLIEF["VVKUNDNRE"].ToString();
                row = 7;
                receiver += System.Convert.ToInt64(KUNDNR).ToString().Trim();
                string phoneR = "";
                var query = from dwKUNDDADR in tbKUNDADR.AsEnumerable()
                            where dwKUNDDADR["KDAKUNDNR"].ToString() == KUNDNR
                            & dwKUNDDADR["KDAADRSL"].ToString() == dwVVLIEF["VVADRSLL"].ToString()
                            select dwKUNDDADR;
                string address = "";
                foreach (DataRow dwKUNDDADR in query)
                {
                    phoneR = "";
                    kdname = GetGB2312Value(dwKUNDDADR["KDANAME1"].ToString().Trim());
                    receiver += "    公司名:" + kdname + Environment.NewLine;
                    string KDASTRASSE1 = GetGB2312Value(dwKUNDDADR["KDASTRASSE1"].ToString()).Trim();
                    string KDASTRASSE2 = GetGB2312Value(dwKUNDDADR["KDASTRASSE2"].ToString()).Trim();
                    address = KDASTRASSE1 + KDASTRASSE2;
                    receiver += "收货地址:" + KDASTRASSE1 + Environment.NewLine;
                    receiver += "                " + KDASTRASSE2 + Environment.NewLine;
                    phoneR += dwKUNDDADR["KDATTEX"].ToString().Trim();
                    phoneR += "/" + dwKUNDDADR["KDATEL"].ToString().Trim();
                    receiver += "收货人电话:" + phoneR;
                    continue;
                }

                exAgent.SetCellValue(3, 7, kdname);
                exAgent.SetCellValue(4, 7, address);
                //exAgent.SetCellValue(row, 2, receiver);
                DataRow dwVKKOPF = tbVKKOPF.Rows[0];
                query = from dwKDANSP in tbKDANSP.AsEnumerable()
                        where dwKDANSP["KDSKUNDNR"].ToString() == KUNDNR
                        & dwKDANSP["KDSANSPNA"].ToString() == dwVKKOPF["VKKANSPRECH"].ToString()
                        select dwKDANSP;
                string KDSANSPNAME = "";
                foreach (DataRow dwKDANSP in query)
                {
                    KDSANSPNAME = "收货人:" + GetGB2312Value(dwKDANSP["KDSANSPNAME"].ToString());
                }
                row = 10;
                KDSANSPNAME += "(" + phoneR + ")";
                //                exAgent.SetCellValue(row, 2, KDSANSPNAME);
                #endregion

                #region=========发件人信息==============================
                row = 2;
                if (dsSource.Tables.Contains("HRSTAMM"))
                {
                    string name, tel;
                    string namekey;
                    string VKKSACHBE = dwVKKOPF["VKKSACHBE"].ToString();
                    string VKKNEUBEN = dwVKKOPF["VKKNEUBEN"].ToString();
                    foreach (DataRow dwHR in dsSource.Tables["HRSTAMM"].Rows)
                    {
                        namekey = dwHR["HRBENID"].ToString();
                        name = GetGB2312Value(dwHR["HRVNAME"].ToString().Trim() + dwHR["HRNNAME"].ToString().Trim());
                        tel = "(" + dwHR["HRDTELH"].ToString().Trim() + "*" + dwHR["HRDTELD"].ToString().Trim() + ")";
                        if (namekey == VKKSACHBE)
                        {

                            //exAgent.SetCellValue(row, 21, "负责人:" + name + tel);
                            exAgent.SetCellValue(7, 7, name + tel);
                        }
                        if (namekey == VKKNEUBEN)
                        {
                            //exAgent.SetCellValue(row + 2, 21, "跟单员:" + name + tel);
                            //exAgent.SetCellValue(7, 7, name + tel);
                        }
                    }
                }
                row = 1;
                string VVLIEFSCH = "'" + System.Convert.ToInt64(dwVVLIEF["VVLIEFSCH"].ToString());
                exAgent.SetCellValue(6, 7, VVLIEFSCH);
                string printdate = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
                exAgent.SetCellValue(4, 24, printdate);
                #endregion

                #region=========承运人==============================
                /*
                if (dsSource.Tables.Contains("VERSART"))
                {
                    foreach (DataRow dwTemp in dsSource.Tables["VERSART"].Rows)
                    {
                        string VBEZ = "承运人:" + GetGB2312Value(dwTemp["VBEZ"].ToString()).Trim();
                        row = 3;
                        exAgent.SetCellValue(row, 21, VBEZ);
                    }
                }

                if (dsSource.Tables.Contains("TXTKON"))
                {
                    row = 5;
                    string txt = "";
                    string txtShiper = "";
                    foreach (DataRow dwVVTXT in dsSource.Tables["TXTKON"].Rows)
                    {
                        txt = GetGB2312Value(dwVVTXT["TXTKTEXT"].ToString()).Trim();
                        if (txt == "")
                            continue;
                        txtShiper += txt + Environment.NewLine;
                    }
                    exAgent.SetCellValue(row, 21, txtShiper);
                }
                */
                #endregion

                #region=========Header==============================
                row = 11;

                //exAgent.SetCellValue(row, 4, kdname);
                string VKPMEEH = dsSource.Tables["VKPOS"].Rows[0]["VKPMEEH"].ToString();
                //exAgent.SetCellValue(row, 13, VKPMEEH);
                //                exAgent.SetCellValue(row, 22, "贵司合同号");
                row = 12;
                //FillinHeader(12);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void FillinHeader(int rowx, bool flagfull = true)
        {
            try
            {
                bool flgbold = true;
                if (flagfull)
                {
                    exAgent.SetCellValue(rowx, 1, "Order No", flgbold);
                    exAgent.SetCellValue(rowx, 7, "Article", flgbold);
                    exAgent.SetCellValue(rowx, 13, "Color", flgbold);

                    //exAgent.SetCellValue(rowx, 19, "Packages",flgbold);
                    exAgent.SetCellValue(rowx, 23, "Rolls", flgbold);
                    exAgent.SetCellValue(rowx, 25, "Length", flgbold);
                    exAgent.SetCellValue(rowx, 29, "N.Kg", flgbold);
                    exAgent.SetCellValue(rowx, 31, "G.Kg", flgbold);
                }

                exAgent.SetCellValue(rowx + 3, 1, "No.", flgbold);
                exAgent.SetCellValue(rowx + 3, 5, "YDS", flgbold);
                exAgent.SetCellValue(rowx + 3, 7, "Lot No.", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 1 + 1, "No.", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 1 + 5, "YDS", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 1 + 7, "Lot No.", flgbold);

                exAgent.SetCellValue(rowx + 3, 8 * 2 + 1, "No.", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 2 + 5, "YDS", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 2 + 7, "Lot No.", flgbold);

                exAgent.SetCellValue(rowx + 3, 8 * 3 + 1, "No.", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 3 + 5, "YDS", flgbold);
                exAgent.SetCellValue(rowx + 3, 8 * 3 + 7, "Lot No.", flgbold);

                exAgent.SetCellValue(rowx, 17, "Order Qty", flgbold);
                exAgent.SetCellValue(rowx + 2, 1, "Your COLOR:", flgbold);
                exAgent.Merge(rowx + 2, 1, rowx + 2, 5);
                /*exAgent.SetCellValue(rowx + 2, 15, "匹长列表");
                exAgent.SetCellValue(rowx + 3, 1, "色光");
                exAgent.SetCellValue(rowx + 3, 3, "包号");
                exAgent.SetCellValue(rowx + 3, 5, "段数");
                */
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        public void SetTXTValue(string sgb, int x, int y)
        {
            int length = 0;
            string s1 = "";
            string s2 = "";
            try
            {
                s1 = sgb;
                exAgent.SetCellValue(x, y, s1);
                x++;
                return;
                length = sgb.Length;
                if (length <= 50)
                {
                    s1 = sgb;
                    exAgent.SetCellValue(x, y, s1);
                    x++;
                    return;
                }
                s1 = sgb.Substring(0, 50);
                exAgent.SetCellValue(x, y, s1);
                x++;
                s2 = sgb.Substring(50, length - 50);
                exAgent.SetCellValue(x, y, s2);
                x++;
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        private string CreateFile(string ordernumer)
        {
            string filename = "";
            string sheetName = "";
            string newfilename = "";
            try
            {
                string pwd = "";
                sheetName = ordernumer;
                newfilename = ordernumer + ".xls";
                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                List<string> Standards = new List<string>();
                Standards.Add("VVWZ");
                Standards.Add("VKWZ");
                exAgent.CreateNewSheets(newfilename, Standards, pwd);
                filename = exAgent.FileName;
                switch (ReportType)
                { 
                    case "FREE":
                        FillInEXCELFREE(4);
                        break;
                    default:
                        FillInEXCEL(4);
                        break;
                }                
                FillInExcelData(null, 0, 0);
                string localFile = exAgent.FileName;
                //System.Diagnostics.Process.Start("EXCEL.EXE", localFile);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.DeleteSheet("VKWZ");
                exAgent.DeleteSheet("VVWZ");
                exAgent.Save();
                exAgent.Close();
            }
            return filename;
        }

        private void ServerShareFile(string ordernumer, string fileType)
        {

            string destfile, tempfile = "";
            string fileabsname = ordernumer + "_" + date;
            try
            {
                filename = destfolder + "\\" + fileabsname + ".xls"; ;
                destfile = filename;
                if (File.Exists(fileabsname + ".xls"))
                {
                    File.Delete(fileabsname + ".xls");
                }
                tempfile = CreateFile(ordernumer);
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        public void WriteExcelReport(string fileType, string m_lang = "CN")
        {
            bool IsFTP = true;
            try
            {
                lang = m_lang;
                //exAgent._app.Visible = true;
                Helper.GetDate(ref date, ref year, ref month, ref day);
                strCompany = "JLT";
                strExcelFile = @"JLT\JLTVVDOC.xls";
                Standard = "VKWZ";
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTVVDOC.xls"); 
                if (exAgent == null)
                {
                    exAgent = new ExcelX.ExcelLib.ExcelAgent();
                    exAgent.Open(TemplatePath);
                    exAgent.Company = app.company;
                    exAgent.strExcelFile = strExcelFile;
                }
                destfolder = SetFolder(Root, "成品仓库"); 
                #region==========准备程序===================
                DataRow dwVKPOS = dsSource.Tables["VKPOS"].Rows[0];
                string KRS = dsSource.Tables["VKPOS"].Rows[0]["VKPAUFKRS"].ToString().Trim();
                string LFD = dsSource.Tables["VKPOS"].Rows[0]["VKPAUFLFD"].ToString().Trim();
                long flfd = 0;
                flfd = System.Convert.ToInt64(LFD);
                string ordernumer = KRS.Trim() + flfd.ToString();
                #endregion
                exAgent.CheckWorkBook(ordernumer);
                ServerShareFile(ordernumer, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

    }
}
